﻿CREATE TABLE Communes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(63),
    CodePostal varchar(5),
    CodeInsee varchar(5),
    Actif bit NOT NULL,
    CONSTRAINT PK_Communes PRIMARY KEY (Id)
);

CREATE TABLE Voies
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Commune_Id uniqueidentifier NOT NULL,
    Nom varchar(63),
    CONSTRAINT PK_Voies PRIMARY KEY (Id)
);
ALTER TABLE Voies ADD CONSTRAINT FK_Voies_Communes FOREIGN KEY (Commune_Id) REFERENCES Communes(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Voies_Communes ON Voies(Commune_Id);

CREATE TABLE Adresses
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Voie_Id uniqueidentifier NOT NULL,
    Numero varchar(7),
    Complement varchar(255),
    CONSTRAINT PK_Adresses PRIMARY KEY (Id)
);
ALTER TABLE Adresses ADD CONSTRAINT FK_Adresses_Voies FOREIGN KEY (Voie_Id) REFERENCES Voies(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Adresses_Voies ON Adresses(Voie_Id);

CREATE TABLE Personnes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Adresse_Id uniqueidentifier,
    Nom varchar(63) NOT NULL,
    Titre varchar(20),
    TphFixe varchar(25),
    TphPortable varchar(25),
    TphBureau varchar(25),
    Fax varchar(25),
    Fax2 varchar(25),
    Mail varchar(127),
    Actif bit NOT NULL,
    CONSTRAINT PK_Personnes PRIMARY KEY (Id)
);
ALTER TABLE Personnes ADD CONSTRAINT FK_Personnes_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Personnes_Adresses ON Personnes(Adresse_Id);

CREATE TABLE LieuxDeService
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Client_Id uniqueidentifier,
    Adresse_Id uniqueidentifier,
    Statut varchar(MAX) NOT NULL,
    Risque varchar(MAX) NOT NULL,
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_LieuxDeService PRIMARY KEY (Id)
);
ALTER TABLE LieuxDeService ADD CONSTRAINT FK_LieuxDeService_Personnes FOREIGN KEY (Client_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_LieuxDeService_Personnes ON LieuxDeService(Client_Id);
ALTER TABLE LieuxDeService ADD CONSTRAINT FK_LieuxDeService_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_LieuxDeService_Adresses ON LieuxDeService(Adresse_Id);

CREATE TABLE Contacts
(
    Personne_Id uniqueidentifier NOT NULL,
    LieuDeService_Id uniqueidentifier NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Contacts PRIMARY KEY (Personne_Id, LieuDeService_Id)
);
ALTER TABLE Contacts ADD CONSTRAINT FK_Contacts_Personnes FOREIGN KEY (Personne_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Contacts_Personnes ON Contacts(Personne_Id);
ALTER TABLE Contacts ADD CONSTRAINT FK_Contacts_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_Contacts_LieuxDeService ON Contacts(LieuDeService_Id);

CREATE TABLE Branchements
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Personne_Id uniqueidentifier,
    Adresse_Id uniqueidentifier,
    CodeGn varchar(14),
    Materiau varchar(MAX),
    Diametre varchar(MAX),
    Emplacement varchar(MAX),
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_Branchements PRIMARY KEY (Id)
);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Personnes FOREIGN KEY (Personne_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Branchements_Personnes ON Branchements(Personne_Id);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Branchements_Adresses ON Branchements(Adresse_Id);

CREATE TABLE Compteurs
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    LieuDeService_Id uniqueidentifier NOT NULL,
    Branchement_Id uniqueidentifier NOT NULL,
    Adresse_Id uniqueidentifier,
    CodeGn varchar(14),
    Numero varchar(15),
    Diametre varchar(MAX),
    Marque varchar(31),
    Modele varchar(31),
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_Compteurs PRIMARY KEY (Id)
);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_Compteurs_LieuxDeService ON Compteurs(LieuDeService_Id);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_Branchements FOREIGN KEY (Branchement_Id) REFERENCES Branchements(Id);
CREATE INDEX FK_Compteurs_Branchements ON Compteurs(Branchement_Id);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Compteurs_Adresses ON Compteurs(Adresse_Id);

CREATE TABLE Conventions
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    LieuDeService_Id uniqueidentifier NOT NULL,
    Gestionnaire_Id uniqueidentifier NOT NULL,
    Statut varchar(MAX) NOT NULL,
    Numero varchar(8) NOT NULL,
    DateSignature datetime,
    Montant int,
    Remise int,
    Actif varchar(MAX) NOT NULL,
    CONSTRAINT UN_Conventions_Numero PRIMARY KEY (Numero)
);
ALTER TABLE Conventions ADD CONSTRAINT FK_Conventions_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Conventions_LieuxDeService ON Conventions(LieuDeService_Id);
ALTER TABLE Conventions ADD CONSTRAINT FK_Conventions_Personnes FOREIGN KEY (Gestionnaire_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Conventions_Personnes ON Conventions(Gestionnaire_Id);

CREATE TABLE Equipements
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Compteur_Id uniqueidentifier NOT NULL,
    TypeEquipement varchar(MAX),
    Statut varchar(MAX),
    Diametre varchar(MAX),
    Marque varchar(31),
    Modele varchar(31),
    Numero varchar(15),
    Rang varchar(MAX) NOT NULL,
    DatePose datetime,
    Localisation varchar(255),
    Conventionne bit,
    Actif bit NOT NULL,
    CONSTRAINT PK_Equipements PRIMARY KEY (Id)
);
ALTER TABLE Equipements ADD CONSTRAINT FK_Equipements_Compteurs FOREIGN KEY (Compteur_Id) REFERENCES Compteurs(Id);
CREATE INDEX FK_Equipements_Compteurs ON Equipements(Compteur_Id);

CREATE TABLE Programmes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(MAX),
    TypeProgramme varchar(MAX) NOT NULL,
    DateOuverture datetime,
    Delai datetime,
    DateCloture datetime,
    Statut varchar(MAX) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Programmes PRIMARY KEY (Id)
);

CREATE TABLE Documents
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(127),
    Donnees varchar(MAX),
    Fichier varchar(MAX),
    CONSTRAINT PK_Documents PRIMARY KEY (Id)
);

CREATE TABLE Contrats
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    TypeContrat varchar(MAX),
    Nom varchar(63),
    Domaine varchar(MAX),
    Actif bit NOT NULL,
    CONSTRAINT PK_Contrats PRIMARY KEY (Id)
);

CREATE TABLE Dossiers
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    LieuDeService_Id uniqueidentifier NOT NULL,
    Programme_Id uniqueidentifier,
    TypeDossier varchar(MAX),
    DateOuverture datetime,
    Delai datetime,
    DateCloture datetime,
    Statut varchar(MAX) NOT NULL,
    Commentaire varchar(MAX),
    CONSTRAINT PK_Dossiers PRIMARY KEY (Id)
);
ALTER TABLE Dossiers ADD CONSTRAINT FK_Dossiers_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Dossiers_LieuxDeService ON Dossiers(LieuDeService_Id);
ALTER TABLE Dossiers ADD CONSTRAINT FK_Dossiers_Programmes FOREIGN KEY (Programme_Id) REFERENCES Programmes(Id) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Dossiers_Programmes ON Dossiers(Programme_Id);

CREATE TABLE Etapes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Dossier_Id uniqueidentifier NOT NULL,
    TypeEtape varchar(MAX) NOT NULL,
    DatePlanification datetime,
    DateOuverture datetime,
    Delai datetime,
    DateRealisation datetime,
    Statut varchar(MAX) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Etapes PRIMARY KEY (Id)
);
ALTER TABLE Etapes ADD CONSTRAINT FK_Etapes_Dossiers FOREIGN KEY (Dossier_Id) REFERENCES Dossiers(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Etapes_Dossiers ON Etapes(Dossier_Id);

CREATE TABLE Actions
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Etape_Id uniqueidentifier NOT NULL,
    TypeAction varchar(MAX) NOT NULL,
    DatePlanification datetime,
    DateOuverture datetime,
    Acteur varchar(68),
    Delai datetime,
    DateRealisation datetime,
    Statut varchar(MAX) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Actions PRIMARY KEY (Id)
);
ALTER TABLE Actions ADD CONSTRAINT FK_Actions_Etapes FOREIGN KEY (Etape_Id) REFERENCES Etapes(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Actions_Etapes ON Actions(Etape_Id);

CREATE TABLE Volumes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Compteur_Id uniqueidentifier NOT NULL,
    DateValeur datetime,
    Valeur bigint,
    CONSTRAINT PK_Volumes PRIMARY KEY (Id)
);
ALTER TABLE Volumes ADD CONSTRAINT FK_Volumes_Compteurs FOREIGN KEY (Compteur_Id) REFERENCES Compteurs(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Volumes_Compteurs ON Volumes(Compteur_Id);

CREATE TABLE Interventions
(
    Id uniqueidentifier NOT NULL,
    Equipement_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_Interventions PRIMARY KEY (Id)
);
ALTER TABLE Interventions ADD CONSTRAINT FK_Interventions_Actions FOREIGN KEY (Id) REFERENCES Actions(Id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Interventions ADD CONSTRAINT FK_Interventions_Equipements FOREIGN KEY (Equipement_Id) REFERENCES Equipements(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Interventions_Equipements ON Interventions(Equipement_Id);

CREATE TABLE Bordereaux
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    CodePharos varchar(12) NOT NULL,
    Libelle varchar(127) NOT NULL,
    PrixUnitaire varchar(MAX) NOT NULL,
    CONSTRAINT PK_Bordereaux PRIMARY KEY (Id)
);

CREATE TABLE ActionsDocuments
(
    Action_Id uniqueidentifier NOT NULL,
    Document_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_ActionsDocuments PRIMARY KEY (Action_Id, Document_Id)
);
ALTER TABLE ActionsDocuments ADD CONSTRAINT FK_ActionsDocuments_Actions FOREIGN KEY (Action_Id) REFERENCES Actions(Id);
CREATE INDEX FK_ActionsDocuments_Actions ON ActionsDocuments(Action_Id);
ALTER TABLE ActionsDocuments ADD CONSTRAINT FK_ActionsDocuments_Documents FOREIGN KEY (Document_Id) REFERENCES Documents(Id);
CREATE INDEX FK_ActionsDocuments_Documents ON ActionsDocuments(Document_Id);

CREATE TABLE LieuxDeServiceContrats
(
    LieuDeService_Id uniqueidentifier NOT NULL,
    Contrat_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_LieuxDeServiceContrats PRIMARY KEY (LieuDeService_Id, Contrat_Id)
);
ALTER TABLE LieuxDeServiceContrats ADD CONSTRAINT FK_LieuxDeServiceContrats_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_LieuxDeServiceContrats_LieuxDeService ON LieuxDeServiceContrats(LieuDeService_Id);
ALTER TABLE LieuxDeServiceContrats ADD CONSTRAINT FK_LieuxDeServiceContrats_Contrats FOREIGN KEY (Contrat_Id) REFERENCES Contrats(Id);
CREATE INDEX FK_LieuxDeServiceContrats_Contrats ON LieuxDeServiceContrats(Contrat_Id);

GO

